import openpyxl
import datetime
import time
import os
import sys
import logging
import configparser
from hanchlogger import logger
import random

timeCur = datetime.datetime.now()
timeCurStr = '{}-{}-{} {}:{}:{}'.format(timeCur.year, timeCur.month, timeCur.day, timeCur.hour, timeCur.minute, timeCur.second)
dirPath = os.path.dirname(os.path.realpath(sys.executable))
# dirPath ='\\\\172.16.10.175\\共享文件\\数据组\\2024年文件\\考勤统计表\\1月'

logFile = dirPath + '\\KaoQinShengCheng.log'
logger.get_log(logFile, 'LoggerKaoQinShengCheng', logging.DEBUG)

iniFilePathCur = dirPath + '\\KaoQinShengCheng.ini'
iniConfigMust = configparser.ConfigParser(allow_no_value=True)
iniConfigMust.read(iniFilePathCur, encoding='utf-8')

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp()-60*60*24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))

timeTomorrowStr = datetime.datetime.fromtimestamp(timeCur.timestamp()+60*60*24).strftime('%Y-%m-%d')
timeTomorrowStr = timeTomorrowStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeTomorrow = datetime.datetime.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrow = time.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrowStamp = int(time.mktime(timeTomorrow))
strTomorrow = '{}年{}月{}日'.format(datatimeTomorrow.year, datatimeTomorrow.month, datatimeTomorrow.day)
logger.debug('明天（日期）：{}    明天（时间戳）：{}'.format(timeTomorrowStr, timeTomorrowStamp))
# 判断是否是大月  大月值班天数为19天 否则值班天数为18天
listMonthDa = [1, 3, 5, 7, 8, 10, 12]
monthLast = timeCur.month - 1
yearLast = timeCur.year

if timeCur.day >= 20:
    monthLast = timeCur.month
else:
    if timeCur.month == 1:
        monthLast = 12
        yearLast = timeCur.year - 1

dayZhiBan = 12
if monthLast in listMonthDa:
    dayZhiBan = 13


class inFoStaff(object):
    def __init__(self):
        self.name = ''
        self.listJiaBan = []
        self.listZhiBan = []
        self.listQuanQin = []


fileKaoQinMuBan = dirPath + '\\考勤模板.xlsx'
fileKaoQinsave = dirPath + '\\拱辰街道{}年{}月考勤.xlsx'.format(timeCur.year, monthLast)

# 打开 考勤模板.xlsx
bookKaoQinMuBan = openpyxl.load_workbook(fileKaoQinMuBan, data_only=True)
sheetJieDaoZhiBan = bookKaoQinMuBan['街道值班人员']
maxRowSheetJieDaoZhiBan = sheetJieDaoZhiBan.max_row
maxColSheetJieDaoZhiBan = sheetJieDaoZhiBan.max_column
logger.debug('街道值班人员列表 -------行数：{}行    列数：{}列'.format(maxRowSheetJieDaoZhiBan, maxColSheetJieDaoZhiBan))
listJieDaoZhiBan = {}
for row in range(2, maxRowSheetJieDaoZhiBan + 1):
    valueName = '{}'.format(sheetJieDaoZhiBan.cell(row, 2).value).strip()
    valueZhiBan = '{}'.format(sheetJieDaoZhiBan.cell(row, 3).value).strip()
    logger.debug('第{}列数据：{}'.format(row, valueName))
    if valueName != '':
        # listJieDaoZhiBan[valueName] = valueZhiBan.split(',')
        listJieDaoZhiBanCopy = valueZhiBan.split(',')
        listJieDaoZhiBanCopy2 = []
        for item in listJieDaoZhiBanCopy:
            listJieDaoZhiBanCopy2.append(int(item) + 2)
        listJieDaoZhiBan[valueName] = listJieDaoZhiBanCopy2
logger.debug('街道值班人员:{} '.format(listJieDaoZhiBan))


sheetKaoQinMuBan = bookKaoQinMuBan['考勤表']
maxRowKaoQinMuBan = sheetKaoQinMuBan.max_row
maxColKaoQinMuBan = sheetKaoQinMuBan.max_column
logger.debug('考勤表 -------行数：{}行    列数：{}列'.format(maxRowKaoQinMuBan, maxColKaoQinMuBan))

sheetKaoQinMuBan.cell(1, 1).value = '拱辰街道{}年{}月考勤表'.format(yearLast, monthLast)
maxDayMonth = 0
rowStart = 0
rowFlag = 0
maxRowStaff = 0
for row in range(2, maxRowKaoQinMuBan + 1):
    valueName = '{}'.format(sheetKaoQinMuBan.cell(row, 2).value).strip()
    logger.debug('第{}列数据：{}'.format(row, valueName))
    if valueName == '是否假期':
        rowFlag = row
        rowStart = row + 1
        maxRowStaff = sheetKaoQinMuBan.cell(row, 1).value
        break

listJiaQiFaDing = []
listJiaQi = []
listWorkDay = []
for col in range(3, maxColKaoQinMuBan + 1):
    valueName = '{}'.format(sheetKaoQinMuBan.cell(rowFlag, col).value).strip()
    logger.debug('标记列第{}行数据：{}'.format(col, valueName))
    if valueName == '' or valueName == 'None':
        break
    maxDayMonth = maxDayMonth + 1
    if valueName == '1':
        listJiaQi.append(col)
    elif valueName == '2':
        listJiaQiFaDing.append(col)
        listJiaQi.append(col)
    else:
        listWorkDay.append(col)

logger.debug('listJiaQi 数据：{}'.format(listJiaQi))
logger.debug('listWorkDay 数据：{}'.format(listWorkDay))

listAllStaff = {}
for row in range(rowStart, rowStart + maxRowStaff):
    inFoStaffDemo = inFoStaff()
    nameStaff = '{}'.format(sheetKaoQinMuBan.cell(row, 2).value).strip()
    if nameStaff == '' or nameStaff == 'None':
        break
    inFoStaffDemo.name = nameStaff
    listJiaQiCopy = listJiaQi.copy()
    listWorkDayCopy = listWorkDay.copy()
    # dayZhiBanCur = dayZhiBan
    if nameStaff in listJieDaoZhiBan:
        dateZhiBan = listJieDaoZhiBan[nameStaff]
        logger.debug('{} 值班日期为：{}'.format(nameStaff, dateZhiBan))
        logger.debug('listJiaQiCopy：{}'.format(listJiaQiCopy))
        logger.debug('listWorkDayCopy：{}'.format(listWorkDayCopy))
        for dateZhiBanCur in dateZhiBan:
            logger.debug('判断{} {}月{}日 值班情况'.format(nameStaff, timeCur.month, dateZhiBanCur))
            if dateZhiBanCur in listJiaQiCopy:
                inFoStaffDemo.listZhiBan.append(dateZhiBanCur)
                listJiaQiCopy.remove(dateZhiBanCur)
                logger.debug('{} {}月{}日  假期值班'.format(nameStaff, timeCur.month, dateZhiBanCur))
                logger.debug('listJiaQiCopy 数据：{}'.format(listJiaQiCopy))
            if dateZhiBanCur in listWorkDayCopy:
                inFoStaffDemo.listZhiBan.append(dateZhiBanCur)
                listWorkDayCopy.remove(dateZhiBanCur)
                logger.debug('{} {}月{}日 日常值班'.format(nameStaff, timeCur.month, dateZhiBanCur))

    while len(inFoStaffDemo.listJiaBan) < 5:
        numRandom = random.choice(listJiaQiCopy)
        inFoStaffDemo.listJiaBan.append(numRandom)
        listJiaQiCopy.remove(numRandom)
        logger.debug('numRandom 数据：{}'.format(numRandom))
        logger.debug('listJiaQiCopy 数据：{}'.format(listJiaQiCopy))
        logger.debug('inFoStaffDemo.listJiaBan 数据：{}'.format(inFoStaffDemo.listJiaBan))
    for value in listJiaQiCopy:
        inFoStaffDemo.listZhiBan.append(value)
        logger.debug('inFoStaffDemo.listZhiBan 数据：{}'.format(inFoStaffDemo.listZhiBan))
    while len(inFoStaffDemo.listZhiBan) < dayZhiBan:
        numRandom = random.choice(listWorkDayCopy)
        inFoStaffDemo.listZhiBan.append(numRandom)
        listWorkDayCopy.remove(numRandom)
    for value in listWorkDayCopy:
        inFoStaffDemo.listQuanQin.append(value)
    inFoStaffDemo.listJiaBan.sort()
    inFoStaffDemo.listZhiBan.sort()
    inFoStaffDemo.listQuanQin.sort()
    listAllStaff[nameStaff] = inFoStaffDemo
    for colIndex in range(3, maxDayMonth + 3 + 1):
        if colIndex in inFoStaffDemo.listJiaBan:
            sheetKaoQinMuBan.cell(row, colIndex).value = '☆'
        if colIndex in inFoStaffDemo.listZhiBan:
            sheetKaoQinMuBan.cell(row, colIndex).value = '√Z'
        if colIndex in inFoStaffDemo.listQuanQin:
            sheetKaoQinMuBan.cell(row, colIndex).value = '√'
    sheetKaoQinMuBan.cell(row, maxDayMonth + 3).value = maxDayMonth - len(listJiaQi)

for index in listAllStaff:
    logger.debug('{} 加班数据：{}'.format(index, listAllStaff[index].listJiaBan))
    logger.debug('{} 值班数据：{}'.format(index, listAllStaff[index].listZhiBan))
    logger.debug('{} 全勤数据：{}'.format(index, listAllStaff[index].listQuanQin))

sheetJiaBan = bookKaoQinMuBan['加班统计表']
maxRowSheetJiaBan = sheetJiaBan.max_row
maxColSheetJiaBan = sheetJiaBan.max_column
logger.debug('加班统计表 -------行数：{}行    列数：{}列'.format(maxRowSheetJiaBan, maxColSheetJiaBan))

sheetJiaBan.cell(1, 1).value = '拱辰街道{}年{}月加班情况统计表'.format(yearLast, monthLast)
for row in range(4, maxRowStaff + 4):
    nameStaff = '{}'.format(sheetJiaBan.cell(row, 2).value).strip()
    if nameStaff == '' or nameStaff == 'None':
        break
    titleJiaBan = ''
    for dayJiaBan in listAllStaff[nameStaff].listJiaBan:
        if titleJiaBan == '':
            titleJiaBan = titleJiaBan + '{}.{}'.format(monthLast, dayJiaBan - 2)
        else:
            titleJiaBan = titleJiaBan + '/{}.{}'.format(monthLast, dayJiaBan - 2)
    sheetJiaBan.cell(row, 3).value = titleJiaBan

sheetZhiBan = bookKaoQinMuBan['值班统计表']
maxRowSheetZhiBan = sheetZhiBan.max_row
maxColSheetZhiBan = sheetZhiBan.max_column
logger.debug('值班统计表 -------行数：{}行    列数：{}列'.format(maxRowSheetZhiBan, maxColSheetZhiBan))

sheetZhiBan.cell(2, 6).value = '{}年{}月'.format(yearLast, monthLast)
for row in range(5, maxRowStaff + 5):
    nameStaff = '{}'.format(sheetZhiBan.cell(row, 2).value).strip()
    if nameStaff == '' or nameStaff == 'None':
        break
    logger.debug('{} 加班数据：{}'.format(index, listAllStaff[index].listJiaBan))
    logger.debug('{} 值班数据：{}'.format(index, listAllStaff[index].listZhiBan))
    logger.debug('{} 全勤数据：{}'.format(index, listAllStaff[index].listQuanQin))
    sheetZhiBan.cell(row, 3).value = maxDayMonth - len(listJiaQi)
    sheetZhiBan.cell(row, 4).value = dayZhiBan - (len(listJiaQi) - len(listAllStaff[nameStaff].listJiaBan))
    numZhiBanFaDing = 0
    for daFading in listJiaQiFaDing:
        if daFading in listAllStaff[nameStaff].listZhiBan:
            numZhiBanFaDing = numZhiBanFaDing + 1
    sheetZhiBan.cell(row, 5).value = len(listJiaQi) - len(listAllStaff[nameStaff].listJiaBan) - numZhiBanFaDing
    if numZhiBanFaDing != 0:
        sheetZhiBan.cell(row, 6).value = numZhiBanFaDing

bookKaoQinMuBan.save(fileKaoQinsave)
